'''
insert into ENT_RISKPOINT(ID,STATUS,CERATOR,CERDATE,MODIFIER,FIRSTTYPE,SECONDTYPE,
THIRDTYPE,INDUSTRYTYPE,PLACE,WXYS,SGLX,FFCS,YJ)
values(sys_guid(),0,'超级管理员',sysdate,'超级管理员',sysdate,'1','2','3','hy','cs','ys','yfs','zy','yj')
'''
import openpyxl
import re

fname = r'D:/2.xlsx'
wb = openpyxl.load_workbook(fname)

t = wb['Sheet1']

cot = []
for i in range(1,t.max_row):
    cot1 = []
    res = t.cell(row=i,column=1).value
    if res == '单位类' or res == '公共领域类':
        for j in range(1, 10):
            res1 = t.cell(row=i, column=j).value
            cot1.append(res1)
    cot.append(cot1)

for i in cot:
    if len(i)>1:
        bl = "insert into ENT_RISKPOINT(ID,STATUS,CERATOR,CERDATE,MODIFIER,FIRSTTYPE,SECONDTYPE,\
THIRDTYPE,INDUSTRYTYPE,PLACE,WXYS,SGLX,FFCS,YJ)\
values(sys_guid(),0,'超级管理员',sysdate,'超级管理员',sysdate,'%s'" % i[0] +",'%s'"%i[1]+\
             ",'%s'"%i[2]+",'%s'"%i[3]+",'%s'"%i[4]+",'%s'"%i[5]+",'%s'"%i[6]+",'%s'"%i[7]+",'%s'"%i[8]+")"
        print(bl)
t1_r = re.compile(r'\d+$')
t2_r = re.compile(r'\d+\.\d+$')
t3_r = re.compile(r'\d+\.\d+\.\d+$')
for j in range(4,7):
    #循环把赋值
    qz = []
    for i in range(1, t.max_row):
        res = t.cell(row=i, column=j)
        if res.value == None:
            res.value = qz[-1]
            qz.append(res.value)
        else:
            qz.append(res.value)

t1 = []
t2 = []
t3 = []
for i in range(1, t.max_row):
    res1 = t.cell(row=i, column=1).value
    s = str(res1).split()
    if len(s) == 1:
        if s[0] == 'None' or t1_r.match(s[0]):
            t.cell(row=i, column=1).value = t1[-1]
            t.cell(row=i, column=2).value = t2[-1]
            t.cell(row=i, column=3).value = t3[-1]
    if len(s) > 1:
        if t1_r.match(s[0]):
            t1.append(s[1])
        if t2_r.match(s[0]):
            t2.append(s[1])
        if t3_r.match(s[0]):
            t3.append(s[1])

wb.save(r'D:/2.xlsx')